import configparser
import logging
import sys
import numpy as np
import pandas as pd
from sqlalchemy import create_engine, MetaData, Column, Table, String, TIMESTAMP, Double, PrimaryKeyConstraint, inspect,text

def segment_five(station_id, inst_id, q_date):
    tm1 = q_date + ' 00:00:00'
    tm2 = q_date + ' 23:59:59'
    query = f"""SELECT * FROM tmk_2401 WHERE `TS` BETWEEN '{tm1}' AND '{tm2}' ORDER BY `TS`; """
    query = text(query)
    df_tmk_2401 = pd.read_sql_query(query, con=engine.connect())
    df = df_tmk_2401[df_tmk_2401['solenoid_valves'].isin([0,2,3])]
    df_five = pd.DataFrame(columns=['STATION_CODE','DATE','TIME','SOLENOID_VALVES','H2O','CO2_CORR','CO','CH4','THIS_VALUE'])

    df['SampleType'] = df['solenoid_valves']

    interval_start = df['TS'].iloc[0]
    last_time = df['TS'].iloc[-1]
    old_type = df['SampleType'].iloc[0]

    while last_time >= interval_start:
        print(interval_start)
        interval_end_3 = interval_start + pd.Timedelta(minutes=3)
        interval_end_5 = interval_start + pd.Timedelta(minutes=5)

        df_every_5_minutes = df[(df['TS'] >= interval_start) & (df['TS'] < interval_end_5)]
        df_every_last_2_minutes = df[(df['TS'] >= interval_end_3) & (df['TS'] < interval_end_5)]
        if df_every_5_minutes['SampleType'].nunique() == 1:
            new_type = df_every_5_minutes['SampleType'].iloc[0]
            if old_type != new_type:
              print('SampleType change time: %s' % interval_start)  
              old_type = new_type
    

            # deal with five minutes table
            five = {}
            five['STATION_CODE'] = station_id
            #five['INST_ID'] = inst_id
            
            five['DATE'] = interval_end_5.strftime('%Y-%m-%d')
            five['TIME'] = interval_end_5.strftime('%X')
            five['SOLENOID_VALVES'] = new_type
            five['H2O'] = df_every_last_2_minutes['H2O'].mean()
            five['CO2_CORR'] = df_every_last_2_minutes['CO2_dry'].mean()
            five['CO'] = df_every_last_2_minutes['CO'].mean()
            five['CH4'] = df_every_last_2_minutes['CH4'].mean()
            five['THIS_VALUE'] = new_type
            #five.to_sql('five_ghg_table', engine, index=False, if_exists='append')
            df_five.loc[len(df_five)] = five

            interval_start = interval_end_5                
        else:
                interval_start = df_every_5_minutes['TS'][df_every_5_minutes['SampleType'] != df_every_5_minutes['SampleType'].iloc[0]].iloc[0]
                print('SampleType change time: %s' % interval_start)
    print(df_five)
    df_five.to_sql('five_ghg_table', con=engine, if_exists='append', index=False)

if __name__ == '__main__':
    config = configparser.ConfigParser()
    config.read('config.ini')
    # Extract log file path
    log_file_path = config['LOGGING']['log_file_path']
    # Configure logging
    logging.basicConfig(filename=log_file_path, level=logging.INFO)
    # Get logger instance
    logger = logging.getLogger(__name__)

    # Extract database connection parameters
    db_engine = config['DATABASE']['engine']
    db_host = config['DATABASE']['host']
    db_port = config['DATABASE']['port']
    db_name = config['DATABASE']['name']
    db_user = config['DATABASE']['user']
    db_password = config['DATABASE']['password']

    # Construct the database URL
    db_url = f'{db_engine}://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}'

    engine = create_engine(db_url)
    metadata = MetaData()

    date_time = sys.argv[1]
    segment_five('58448', 'S024', date_time)
    print("---Done!---")
